package org.svnadmin.dao;

import org.springframework.stereotype.Repository;
import org.svnadmin.constant.Constants;
import org.svnadmin.entity.PjGrUsr;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 项目的组的用户
 *
 * @author <a href="mailto:yuanhuiwu@gmail.com">Huiwu Yuan</a>
 */
@Repository(PjGrUsrDao.BEAN_NAME)
public class PjGrUsrDao extends Dao {
    /**
     * Bean名称
     */
    public static final String BEAN_NAME = "pjGrUsrDao";

    /**
     * @param pj  项目
     * @param gr  组
     * @param usr 用户
     * @return 组用户
     */
    public PjGrUsr get(String pj, String gr, String usr) {
        String sql = "select a.pj,a.usr,a.gr,b.name as usrname from pj_gr_usr a left join usr b on (a.usr=b.usr) where a.pj = ? and a.gr=? and a.usr=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, gr);
            pstmt.setString(index++, usr);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return readPjGrUsr(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
        return null;
    }

    /**
     * @param pj 项目
     * @param gr 组
     * @return 组用户列表
     */
    public List<PjGrUsr> getList(String pj, String gr) {
        String sql = "select a.pj,a.usr,a.gr,b.name as usrname from pj_gr_usr a left join usr b on (a.usr = b.usr) where a.pj=? and a.gr=? order by a.usr";
        List<PjGrUsr> list = new ArrayList<PjGrUsr>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, gr);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(readPjGrUsr(rs));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
    }

    /**
     * 项目的组用户列表(用户可能为空),导出authz文件时使用
     *
     * @param pj 项目
     * @return 项目的组用户列表
     */
    public List<PjGrUsr> getList(String pj) {
        // String sql =
        // "select pj,usr,gr from pj_gr_usr where pj=? order by gr,usr";
        String sql = "select a.pj,a.gr,b.usr,c.name as usrname from pj_gr a left join pj_gr_usr b on (a.pj=b.pj and a.gr=b.gr) left join usr c on (b.usr = c.usr) where a.pj=? order by a.gr,b.usr";
        List<PjGrUsr> list = new ArrayList<PjGrUsr>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(readPjGrUsr(rs));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
    }

    /**
     * 有相同的svn root的项目的组用户列表(用户可能为空),导出authz文件时使用
     *
     * @param rootPath svn root
     * @return 有相同的svn root的项目组用户
     */
    public List<PjGrUsr> getListByRootPath(String rootPath) {
        // String sql =
        // "select pj,usr,gr from pj_gr_usr where pj in (select distinct pj from pj
        // where type=? and path like ?) order by pj,gr,usr";
        String sql = "select a.pj,a.gr,b.usr,c.name as usrname from pj_gr a left join pj_gr_usr b on (a.pj=b.pj and a.gr=b.gr) left join usr c on (b.usr=c.usr) "
                + " where a.pj in (select distinct pj from pj where type=? and path like ?) order by a.pj,a.gr,b.usr";
        List<PjGrUsr> list = new ArrayList<PjGrUsr>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, Constants.HTTP_MUTIL);
            pstmt.setString(index++, rootPath + "%");// TODO 大小写敏感?

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(readPjGrUsr(rs));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
    }

    /**
     * @param rs ResultSet
     * @return 组用户
     * @throws SQLException jdbc异常
     */
    PjGrUsr readPjGrUsr(ResultSet rs) throws SQLException {
        PjGrUsr result = new PjGrUsr();
        result.setPj(rs.getString("pj"));
        result.setUsr(rs.getString("usr"));
        result.setGr(rs.getString("gr"));
        result.setUsrName(rs.getString("usrname"));
        return result;
    }

    /**
     * 删除
     *
     * @param pj  项目
     * @param gr  组
     * @param usr 用户
     */
    public void delete(String pj, String gr, String usr) {
        String sql = "delete from pj_gr_usr where pj = ? and gr=? and usr=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, gr);
            pstmt.setString(index++, usr);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 删除
     *
     * @param usr 用户
     */
    public void deleteUsr(String usr) {
        String sql = "delete from pj_gr_usr where usr = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, usr);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 删除
     *
     * @param pj 项目
     * @param gr 组
     */
    public void deletePjGr(String pj, String gr) {
        String sql = "delete from pj_gr_usr where pj = ? and gr = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, gr);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 删除
     *
     * @param pj 项目
     */
    public void deletePj(String pj) {
        String sql = "delete from pj_gr_usr where pj = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 保存
     *
     * @param pjGrUsr 项目用户
     */
    public void save(PjGrUsr pjGrUsr) {
        if (this.get(pjGrUsr.getPj(), pjGrUsr.getGr(), pjGrUsr.getUsr()) == null) {
            String sql = "insert into pj_gr_usr (pj,usr,gr) values (?,?,?)";
            Connection conn = null;
            PreparedStatement pstmt = null;
            try {
                conn = this.getConnection();
                pstmt = conn.prepareStatement(sql);
                int index = 1;
                pstmt.setString(index++, pjGrUsr.getPj());
                pstmt.setString(index++, pjGrUsr.getUsr());
                pstmt.setString(index++, pjGrUsr.getGr());

                pstmt.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                this.close(null, pstmt, conn);
            }
        }
    }

    /**
     * 从模板库复制权限
     */
    public void copydemo(String demo, String pj) {
        List<PjGrUsr> list = new ArrayList<PjGrUsr>();

        list = getList(demo);
        for (int i = 0; i < list.size(); i++) {
            PjGrUsr pjGrUsr = list.get(i);
            pjGrUsr.setPj(pj);
            if (pjGrUsr.getUsr() == null) {
                continue;
            }
            save(pjGrUsr);
        }
    }

}